package com.cn.ccp.service.classschedule;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.cn.ccp.bean.ClassScheduleBean;
import com.cn.ccp.bean.ClassScheduleSettingsBean;
import com.cn.ccp.service.BasicService;
import com.cn.ccp.util.Contants;
 

/**
 * 课程表service
 * @author Administrator
 *
 */
public class ClassScheduleService extends BasicService{
	private Log log = LogFactory.getLog(ClassScheduleService.class);
		
	
	/**
	 * 添加课程表
	 * @param bo
	 * @return
	 */
	public boolean save(final ClassScheduleBean bo){
		log.info("1-------------------------------------------");
		final String sql = "insert into class_schedule(specialty_id,grade_id,class_schedule_name,term_setting_id,teacher_id) values (?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();  
		jdbcTemplate.update(  
				   new PreparedStatementCreator() {  
				    public PreparedStatement createPreparedStatement(Connection con) throws SQLException   
				    {  
					     PreparedStatement ps =con.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS);
					     ps.setString(1,  bo.getSpecialtyId());
					     ps.setString(2,  bo.getGrade());
					     ps.setString(3,  bo.getName());
					     ps.setString(4,  bo.getTermSettingId());
					     ps.setString(5,  bo.getTeacherId());
					     return ps;  
				    }  
				   }, keyHolder);  
		final int scheduleId = keyHolder.getKey().intValue();
		String schedulesql = "select * from class_schedule_settings ";
		final List scheduleList =  jdbcTemplate.query(schedulesql,new ClassScheduleSettingsMapper());
		if(scheduleList != null){
			Thread t = new  Thread(){
				public void run(){
					int len = scheduleList.size();
					ClassScheduleSettingsBean schedueBean;
					 String sql1 = "insert into class_schedule_detail(class_schedule_id,week,class_schedule_setting_id,subjects,stat_week,end_week,teacher_id,class_address,week_type) values  ";
					 StringBuffer sqlBuf = new StringBuffer();
					 sqlBuf.append(sql1);
					 for(int i=0; i<len;i++){
		    			 schedueBean = (ClassScheduleSettingsBean)scheduleList.get(i);
		    			 for(int j=1; j<8;j++){
		    				 sqlBuf.append("(");
		 					sqlBuf.append(scheduleId);
		 					sqlBuf.append(",");
		 					sqlBuf.append(j);
		 					sqlBuf.append(",");
		 					sqlBuf.append(schedueBean.getId());
		 					sqlBuf.append(",");
		 					sqlBuf.append("'"+Contants.SCHEDULE_CLASS_SUBJECT_INIT+"'");
		 					sqlBuf.append(",1,17,0,'',");
		 					sqlBuf.append(Contants.SCHEDULE_TYPE_DEFAULT);
		 					if( i< len - 1 || j < 7)
		 						sqlBuf.append("),");
		 					else
		 						sqlBuf.append(")");
		        		 }
		    		 }
					 log.info(sqlBuf);
					 jdbcTemplate.update(sqlBuf.toString());
				}
			};
			t.setDaemon(true);
			t.start();
			try {
				t.join();
			} catch (InterruptedException e) {
				log.error("insert schedul for teacher failed : ", e);
			}
		}
		log.info("2-------------------------------------------");
		return true;
		
	}
	
	
	public boolean update(ClassScheduleBean bo){
		StringBuffer buf = new StringBuffer();
		buf.append("update class_schedule set ");
		buf.append(" specialty_id = ?,");
		buf.append(" grade_id = ?,");
		buf.append(" class_schedule_name = ? ,");
		buf.append(" term_setting_id = ? ");
		buf.append("where id = ?");
		return jdbcTemplate.update(buf.toString() ,new Object[]{bo.getSpecialtyId(),bo.getGrade(),bo.getName(),bo.getTermSettingId(), bo.getId()}) > 0; 
	}
	
	
	public boolean delete(int id) {
		String sql = "delete from class_schedule_detail where class_schedule_id = ?";
		jdbcTemplate.update(sql ,new Object[]{id});
		sql = "delete from class_schedule where id = ?";
		return jdbcTemplate.update(sql ,new Object[]{id}) > 0;
	}
	
	/**
	 *根据id查询学期设置
	 * @param id
	 * @return
	 */
	public Object findClassScheduleById(int id) {
		String sql = "SELECT * FROM class_schedule WHERE id = ?";
		return jdbcTemplate.queryForObject(sql, new Object[] { id }, new ClassScheduleMapper());
	}
	
	public List pageQuery(int start, int pagesize) {
		String sql = "select * from class_schedule where teacher_id=-1 limit ?,? ";
		return jdbcTemplate.query(sql, new Object[] { start,pagesize},new ClassScheduleMapper());
	}
	
	public int getCount() {
		String sql = "select count(1) from class_schedule where teacher_id=-1";
		return jdbcTemplate.queryForInt(sql);
	}
	
	/**
	 * 查询老师的课程表
	 * @param start
	 * @param pagesize
	 * @param userId
	 * @return
	 */
	public List pageQuery(int start, int pagesize,String userId) {
		String sql = "select * from class_schedule where teacher_id=? limit ?,? ";
		return jdbcTemplate.query(sql, new Object[] { userId,start,pagesize},new ClassScheduleMapper());
	}
	
	/**
	 * 返回老师课程表数量
	 * @param userId
	 * @return
	 */
	public int getCount(String  userId) {
		String sql = "select count(1) from class_schedule where teacher_id=?";
		return jdbcTemplate.queryForInt(sql, new Object[] { userId});
	}
	
	
	 class ClassScheduleMapper implements RowMapper {
			@Override
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				ClassScheduleBean bean = new ClassScheduleBean();
				bean.setId(rs.getInt("id"));
				bean.setSpecialtyId(rs.getString("specialty_id"));
				bean.setGrade(rs.getString("grade_id"));
				bean.setName(rs.getString("class_schedule_name"));
				bean.setTermSettingId(rs.getString("term_setting_id"));
				return bean;
			}
	}
	
	 
	 class ClassScheduleSettingsMapper implements RowMapper {
			@Override
			public Object mapRow(ResultSet rs, int i) throws SQLException {
				ClassScheduleSettingsBean bean = new ClassScheduleSettingsBean();
				bean.setId(rs.getInt("id"));
				bean.setNumber(rs.getInt("number"));
				return bean;
			}
		}
	 
}
